﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Linq.Dynamic;
using EIIS.Infrastructure.Repositories;
using EIIS.Infrastructure.Repositories.Business;
using EIIS.Infrastructure.Model.Business;

namespace EIIS.Infrastructure.Repositories.Implement
{
    public class PackageRepository:RepositoryBase<Package>,IPackageRepository
    {
        public IQueryable<Package> LoadAllByPage(out long total, int ZhuangT, int page, int rows, string order, string sort)
        {
            var list = this.LoadAll().Where(p => p.ZhuangT == ZhuangT);
            total = list.LongCount();
            if (!string.IsNullOrEmpty(sort) && !string.IsNullOrEmpty(order))
            {
                list = list.OrderBy(sort + " " + order);
            }
            list = list.Skip((page - 1) * rows).Take(rows);

            return list;
        }

        public Package GetByTiaoMH(string strTiaoMH)
        {
            Package p = null;
            string strHql = "select p from Package as p where p.TiaoMH=:TiaoMH";
            var query = Session.CreateQuery(strHql)
                .SetString("TiaoMH", strTiaoMH)
                .List<Package>();
            if (query != null && query.LongCount() > 0) p = query[0];
            return p;
        }

        public IQueryable<Package> GetAllByZhuangT(int ZhuangT)
        {
            string strHQL = "select p from Package as p where p.ZhuangT=:ZhuangT";
            var query = Session.CreateQuery(strHQL)
               .SetInt32("ZhuangT", ZhuangT)
               .List<Package>().AsQueryable();

            return query;
        }

        public bool SaveMieJ(string MieJGH, string MieJGC, string YouXQ, string ShiXSJ, string MieJR)
        {
            string strHQL = "update Package set MieJSJ=:MieJSJ,MieJR=:MieJR,MieJGH=:MieJGH,MieJGC=:MieJGC,YouXQ=:YouXQ,ShiXSJ=:ShiXSJ,ZhuangT=2 where ZhuangT=20";
            return Session.CreateQuery(strHQL)
                .SetString("MieJSJ", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
                .SetString("MieJR", MieJR)
                .SetString("MieJGH", MieJGH)
                .SetString("MieJGC", MieJGC)
                .SetString("YouXQ", YouXQ)
                .SetString("ShiXSJ", ShiXSJ).ExecuteUpdate() > 0;
        }

        public bool SaveFangF(string LingYKS, string LingYR, string ShiYR, string FaFR)
        {
            string strHQL = "select d.Name from Dictionary as d where d.Id=:Id";
            var query = Session.CreateQuery(strHQL).SetString("Id", LingYR).UniqueResult();
            if (query != null)
            {
                bool ShiFDH = false;
                if (query.ToString() == "兑换")
                {
                    ShiFDH = true;
                }
                strHQL = "update Package set FaFSJ=:FaFSJ,FaFR=:FaFR,LingYKS=:LingYKS,LingYR=:LingYR,ShiYR=:ShiYR,ZhuangT=3,ShiFDH=:ShiFDH where ZhuangT=30";
                return Session.CreateQuery(strHQL)
                    .SetString("FaFSJ", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
                    .SetString("FaFR", FaFR)
                    .SetString("LingYKS", LingYKS)
                    .SetString("LingYR", LingYR)
                    .SetString("ShiYR", ShiYR)
                    .SetBoolean("ShiFDH", ShiFDH)
                    .ExecuteUpdate() > 0;
            }
            else return false;           
        }

        public bool SaveHuiS(string HuiSR)
        {
            string strHQL = "update Package set HuiSR=:HuiSR,HuiSSJ=:HuiSSJ,ZhuangT=4 where ZhuangT=40";
            return Session.CreateQuery(strHQL)
                .SetString("HuiSSJ", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
                .SetString("HuiSR",HuiSR)
                .ExecuteUpdate() > 0;
        }

        public long CountByZhuangT(int ZhuangT)
        {
            string strHQL = "select count(p) from Package as p where p.ZhuangT=:ZhuangT";
            var query = Session.CreateQuery(strHQL)
                .SetInt32("ZhuangT", ZhuangT)
                .UniqueResult();
            if (query != null)
                return Convert.ToInt64(query);
            else return 0;
        }

        public IList<V_Package> GetAllPackageView()
        {
            string strSQL = "select * from V_Package ";
            List<V_Package> list = new List<V_Package>();
            IList query = Session.CreateSQLQuery(strSQL).List();

            if (query != null)
            {
                for (int i = 0; i < query.Count; i++)
                {
                    object[] objs = (object[])query[i];
                    list.Add(new V_Package()
                    {
                        TiaoMFLMC = objs[0].ToString(),
                        TiaoMFLBM = objs[1].ToString(),
                        TiaoMFLBH = objs[2].ToString(),
                        PackageID = objs[3].ToString(),
                        TiaoMH = objs[4].ToString(),
                        BaoBH = objs[5].ToString(),
                        BaoMC = objs[6].ToString(),                        
                        ZhuangT = Convert.ToInt32(objs[7]),
                        BaoZR = objs[8].ToString(),
                        BaoZSJ = objs[9].ToString(),
                        BaoZRQ = string.IsNullOrEmpty(objs[9].ToString()) == true ? "" : DateTime.Parse(objs[9].ToString()).ToString("yyyy-MM-dd"),
                        QingXQGH = objs[10].ToString(),
                        QingXQGC = objs[11].ToString(),
                        MieJR = objs[12].ToString(),
                        MieJSJ = objs[13].ToString(),
                        MieJRQ = string.IsNullOrEmpty(objs[13].ToString()) == true ? "" : DateTime.Parse(objs[13].ToString()).ToString("yyyy-MM-dd"),
                        MieJGH = objs[14].ToString(),
                        MieJGC = objs[15].ToString(),
                        YouXQ = Convert.ToInt32(objs[16]),
                        ShiXSJ = objs[17].ToString(),
                        FaFR = objs[18].ToString(),
                        FaFSJ = objs[19].ToString(),
                        FaFRQ = string.IsNullOrEmpty(objs[19].ToString()) == true ? "" : DateTime.Parse(objs[19].ToString()).ToString("yyyy-MM-dd"),
                        LingYKS = objs[20].ToString(),
                        LingYR = objs[21].ToString(),
                        ShiYR = objs[22].ToString(),
                        ShiFDH = (bool)objs[23],
                        HuiSR = objs[24].ToString(),                                               
                        HuiSSJ = objs[25].ToString(),
                        HuiSRQ = string.IsNullOrEmpty(objs[25].ToString()) == true ? "" : DateTime.Parse(objs[25].ToString()).ToString("yyyy-MM-dd"),
                        QingXQGHBH = objs[26].ToString(),
                        QingXQGCBH = objs[27].ToString(),
                        MieJGHBH = objs[28].ToString(),
                        MieJGCBH = objs[29].ToString(),
                        LingYKSBH = objs[30].ToString(),
                        LingYRBH = objs[31].ToString(),
                        ShiYRBH = objs[32].ToString(), 
                    });
                }
            }
            return list;
        }

        public IList GetInventoryReport()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select baomc,count(baomc) as num from V_Package ");
            sb.Append("where (zhuangt=1 or zhuangt=2 or zhuangt=20 or zhuangt=30) ");
            sb.Append("group by baomc ");
            sb.Append("union all ");
            sb.Append("select baomc,0 as num from V_Package ");
            sb.Append("where zhuangt=0");

            string strSQL = sb.ToString();

            IList query = Session.CreateSQLQuery(strSQL).List();
            return query;
        }

        public IList<Package> GetInventory(string strBaoBM)
        {
            string strHQL = "select p from Package as p where p.ZhuangT in (1,2,20,30)";
            IList<Package> list;
            if (!string.IsNullOrEmpty(strBaoBM))
            {
                strHQL = strHQL + " and p.BaoMC=:BaoMC";
                list = Session.CreateQuery(strHQL).SetString("BaoMC", strBaoBM).List<Package>();
            }
            else
            {
                list = Session.CreateQuery(strHQL).List<Package>();
            }
            return list;
        }

        public IList<Package> GetNoReurns(string strBaoBM)
        {
            string strHQL = "select p from Package as p where p.ZhuangT in (3,40)";
            IList<Package> list;
            if (!string.IsNullOrEmpty(strBaoBM))
            {
                strHQL = strHQL + " and p.BaoMC=:BaoMC";
                list = Session.CreateQuery(strHQL).SetString("BaoMC", strBaoBM).List<Package>();
            }
            else
            {
                list = Session.CreateQuery(strHQL).List<Package>();
            }
            return list;
        }

        public IList<V_Package> GetRecordsByPeriodTime(string begin, string end, string strWhere)
        {
            IList query;
            List<V_Package> list = new List<V_Package>();
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from V_Package where 1=1");
            if (!string.IsNullOrEmpty(begin))
            {
                sb.Append(" and (");
                sb.Append(" cast(baozsj as datetime)>=cast(:begin as datetime)");
                sb.Append(" or cast(miejsj as datetime)>=cast(:begin as datetime)");
                sb.Append(" or cast(fafsj as datetime)>=cast(:begin as datetime)");
                sb.Append(" or cast(huissj as datetime)>=cast(:begin as datetime)");
                sb.Append(" )");
            }
            if (!string.IsNullOrEmpty(end))
            {
                sb.Append(" and (");
                sb.Append(" cast(baozsj as datetime)<dateadd(day,1,cast(:end as datetime))");
                sb.Append(" or cast(miejsj as datetime)<dateadd(day,1,cast(:end as datetime))");
                sb.Append(" or cast(fafsj as datetime)<dateadd(day,1,cast(:end as datetime))");
                sb.Append(" or cast(huissj as datetime)<dateadd(day,1,cast(:end as datetime))");
                sb.Append(" )");
            }
            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(" and " + strWhere);
            }
            string strSQL = sb.ToString();
            NHibernate.ISQLQuery sqlQuery = Session.CreateSQLQuery(strSQL);
            if (!string.IsNullOrEmpty(begin))
            {
                sqlQuery.SetString("begin", begin);
            }
            if (!string.IsNullOrEmpty(end))
            {
                sqlQuery.SetString("end", end);
            }
            query = sqlQuery.List();
            for (int i = 0; i < query.Count; i++)
            {
                object[] objs = (object[])query[i];
                list.Add(new V_Package()
                {
                    TiaoMFLMC = objs[0].ToString(),
                    TiaoMFLBM = objs[1].ToString(),
                    TiaoMFLBH = objs[2].ToString(),
                    PackageID = objs[3].ToString(),
                    TiaoMH = objs[4].ToString(),
                    BaoBH = objs[5].ToString(),
                    BaoMC = objs[6].ToString(),
                    ZhuangT = Convert.ToInt32(objs[7]),
                    BaoZR = objs[8].ToString(),
                    BaoZSJ = objs[9].ToString(),
                    BaoZRQ = string.IsNullOrEmpty(objs[9].ToString()) == true ? "" : DateTime.Parse(objs[9].ToString()).ToString("yyyy-MM-dd"),
                    QingXQGH = objs[10].ToString(),
                    QingXQGC = objs[11].ToString(),
                    MieJR = objs[12].ToString(),
                    MieJSJ = objs[13].ToString(),
                    MieJRQ = string.IsNullOrEmpty(objs[13].ToString()) == true ? "" : DateTime.Parse(objs[13].ToString()).ToString("yyyy-MM-dd"),
                    MieJGH = objs[14].ToString(),
                    MieJGC = objs[15].ToString(),
                    YouXQ = Convert.ToInt32(objs[16]),
                    ShiXSJ = objs[17].ToString(),
                    FaFR = objs[18].ToString(),
                    FaFSJ = objs[19].ToString(),
                    FaFRQ = string.IsNullOrEmpty(objs[19].ToString()) == true ? "" : DateTime.Parse(objs[19].ToString()).ToString("yyyy-MM-dd"),
                    LingYKS = objs[20].ToString(),
                    LingYR = objs[21].ToString(),
                    ShiYR = objs[22].ToString(),
                    ShiFDH = (bool)objs[23],
                    HuiSR = objs[24].ToString(),
                    HuiSSJ = objs[25].ToString(),
                    HuiSRQ = string.IsNullOrEmpty(objs[25].ToString()) == true ? "" : DateTime.Parse(objs[25].ToString()).ToString("yyyy-MM-dd"),
                    QingXQGHBH = objs[26].ToString(),
                    QingXQGCBH = objs[27].ToString(),
                    MieJGHBH = objs[28].ToString(),
                    MieJGCBH = objs[29].ToString(),
                    LingYKSBH = objs[30].ToString(),
                    LingYRBH = objs[31].ToString(),
                    ShiYRBH = objs[32].ToString(),  
                });
            }
            return list;
        }

        public IList<Package> GetDayRecords()
        {           
            string strDay = DateTime.Now.ToString("yyyy-MM-dd");
            string strSQL = "select * from B_Package where substring(baozsj,1,10)=:now or substring(miejsj,1,10)=:now or substring(fafsj,1,10)=:now or substring(huissj,1,10)=:now";
            IList<Package> list = Session.CreateSQLQuery(strSQL).AddEntity(typeof(Package))
                .SetString("now", strDay)
                .List<Package>();

            return list;
        }
    }
}
